package com.example.hhh.repository;

import com.example.hhh.model.Customer;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer> {

    // 1. 基础查询方法

    // 按客户ID查询
    @Override
    Optional<Customer> findById(Integer id);

    // 查询所有客户（分页）
    Page<Customer> findAll(Pageable pageable);

    // 按客户名称精确查询
    Optional<Customer> findByCustomerName(String customerName);

    // 2. 条件查询方法

    // 按客户名称模糊查询
    List<Customer> findByCustomerNameContaining(String keyword);

    // 按行业类型查询
    List<Customer> findByIndustryType(String industryType);

    // 按客户来源查询
    List<Customer> findBySource(String source);

    // 按客户等级查询
    List<Customer> findByCustomerLevel(Integer level);

    // 查询活跃客户
    List<Customer> findByStatus(Integer status);

    // 3. 复杂查询

    // 多条件组合查询
    @Query("SELECT c FROM Customer c WHERE " +
            "(:name IS NULL OR c.customerName LIKE %:name%) AND " +
            "(:industry IS NULL OR c.industryType = :industry) AND " +
            "(:minLevel IS NULL OR c.customerLevel >= :minLevel)")
    List<Customer> searchCustomers(
            @Param("name") String name,
            @Param("industry") String industry,
            @Param("minLevel") Integer minLevel);

    // 4. 统计查询

    // 统计各行业客户数量
    @Query("SELECT c.industryType, COUNT(c) FROM Customer c GROUP BY c.industryType")
    List<Object[]> countByIndustryType();

    // 统计客户来源分布
    @Query("SELECT c.source, COUNT(c) FROM Customer c GROUP BY c.source")
    List<Object[]> countBySource();

    // 5. 更新操作

    // 更新客户状态
    @Modifying
    @Query("UPDATE Customer c SET c.status = :status WHERE c.customerId = :id")
    int updateStatus(@Param("id") Integer id, @Param("status") Integer status);

    // 更新客户等级
    @Modifying
    @Query("UPDATE Customer c SET c.customerLevel = :level WHERE c.customerId = :id")
    int updateLevel(@Param("id") Integer id, @Param("level") Integer level);

    // 6. 原生SQL查询

    // 使用原生SQL查询VIP客户
    @Query(value = "SELECT * FROM Customer WHERE customer_level = 2 AND status = 1",
            nativeQuery = true)
    List<Customer> findVipCustomers();

    // 7. 查询有联系方式的客户（替代原来的 findCustomersWithContacts）
    @Query("SELECT c FROM Customer c WHERE c.contactPhone IS NOT NULL OR c.contactEmail IS NOT NULL")
    List<Customer> findCustomersWithContactInfo();
}